import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.chart import LineChart, BarChart, Reference
from src.models.database import db, PredictionTask, PredictionResult, HistoricalData
import os
import uuid
from datetime import datetime
import json

class ReportService:
    """报表生成服务"""
    
    def __init__(self):
        self.reports_dir = os.path.join(os.path.dirname(__file__), '..', 'reports')
        os.makedirs(self.reports_dir, exist_ok=True)
        
        # 报表模板配置
        self.templates = {
            'product_forecast': {
                'name': '重点产品销售预测模板',
                'description': '包含销售量、单价、成本等详细预测数据',
                'fields': ['产品名称', '销售量', '平均单价', '销售额', '单位成本', '单位毛利', '差异分析']
            },
            'historical_data': {
                'name': '重点产品历史数据模板',
                'description': '历史经营数据汇总和趋势分析',
                'fields': ['产品名称', '年份', '销售数据', '增长率', 'CAGR']
            },
            'comprehensive': {
                'name': '经营指标历史数据+预测模板',
                'description': '集团层面综合经营指标分析',
                'fields': ['经营指标', '历史数据', '预测数据', '行业对比', '影响因素']
            }
        }
    
    def generate_report(self, template_type: str, task_id: str, options: dict = None):
        """生成报表"""
        try:
            # 验证模板类型
            if template_type not in self.templates:
                raise ValueError(f"不支持的模板类型: {template_type}")
            
            # 获取任务和预测结果
            task = PredictionTask.query.filter_by(task_id=task_id).first()
            if not task:
                raise ValueError(f"任务不存在: {task_id}")
            
            if task.status != 'completed':
                raise ValueError("任务尚未完成，无法生成报表")
            
            results = PredictionResult.query.filter_by(task_id=task_id).all()
            if not results:
                raise ValueError("未找到预测结果")
            
            # 根据模板类型生成报表
            if template_type == 'product_forecast':
                file_path = self._generate_product_forecast_report(task, results, options)
            elif template_type == 'historical_data':
                file_path = self._generate_historical_data_report(task, results, options)
            elif template_type == 'comprehensive':
                file_path = self._generate_comprehensive_report(task, results, options)
            else:
                raise ValueError(f"未实现的模板类型: {template_type}")
            
            # 获取文件信息
            file_size = os.path.getsize(file_path)
            filename = os.path.basename(file_path)
            
            return {
                'filename': filename,
                'file_path': file_path,
                'file_size': f"{file_size / 1024 / 1024:.2f} MB",
                'template_type': template_type,
                'generated_at': datetime.now().isoformat()
            }
            
        except Exception as e:
            raise e
    
    def _generate_product_forecast_report(self, task, results, options):
        """生成重点产品销售预测报表"""
        # 创建Excel工作簿
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "重点产品销售预测"
        
        # 设置标题
        ws['A1'] = "重点产品销售预测报表"
        ws['A1'].font = Font(size=16, bold=True)
        ws['A1'].alignment = Alignment(horizontal='center')
        ws.merge_cells('A1:L1')
        
        # 设置报表信息
        ws['A3'] = f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        ws['A4'] = f"任务名称: {task.task_name}"
        ws['A5'] = f"预测年份: {', '.join(json.loads(task.prediction_years))}"
        
        # 设置表头
        headers = [
            '序号', '公司名称', '产品名称', '预测年份',
            '预测销量', '预测单价(元)', '预测销售额(万元)',
            '预测成本(元)', '预测毛利(元)', '量差(万元)',
            '价差(万元)', '成本差异(万元)', '总差异(万元)',
            '置信度', '影响因素分析'
        ]
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=7, column=col, value=header)
            cell.font = Font(bold=True)
            cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
            cell.alignment = Alignment(horizontal='center')
        
        # 填充数据
        for row, result in enumerate(results, 8):
            ws.cell(row=row, column=1, value=row-7)
            ws.cell(row=row, column=2, value=result.company_name)
            ws.cell(row=row, column=3, value=result.product_name)
            ws.cell(row=row, column=4, value=result.prediction_year)
            ws.cell(row=row, column=5, value=round(result.predicted_sales_volume, 0))
            ws.cell(row=row, column=6, value=round(result.predicted_avg_price, 2))
            ws.cell(row=row, column=7, value=round(result.predicted_sales_amount, 1))
            ws.cell(row=row, column=8, value=round(result.predicted_unit_cost, 2))
            ws.cell(row=row, column=9, value=round(result.predicted_unit_profit, 2))
            ws.cell(row=row, column=10, value=round(result.volume_variance, 1))
            ws.cell(row=row, column=11, value=round(result.price_variance, 1))
            ws.cell(row=row, column=12, value=round(result.cost_variance, 1))
            ws.cell(row=row, column=13, value=round(result.total_variance, 1))
            ws.cell(row=row, column=14, value=f"{result.confidence_score*100:.1f}%")
            
            # 影响因素分析（截取前100字符）
            analysis = result.influence_factors_analysis or "暂无分析"
            ws.cell(row=row, column=15, value=analysis[:100] + "..." if len(analysis) > 100 else analysis)
        
        # 设置列宽
        column_widths = [6, 15, 15, 10, 12, 12, 15, 12, 12, 12, 12, 12, 12, 10, 30]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = width
        
        # 添加边框
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        for row in ws.iter_rows(min_row=7, max_row=7+len(results), min_col=1, max_col=15):
            for cell in row:
                cell.border = thin_border
        
        # 添加汇总行
        summary_row = 8 + len(results)
        ws.cell(row=summary_row, column=1, value="合计")
        ws.cell(row=summary_row, column=1).font = Font(bold=True)
        
        # 计算汇总数据
        total_sales_amount = sum(r.predicted_sales_amount for r in results)
        total_variance = sum(r.total_variance for r in results)
        avg_confidence = sum(r.confidence_score for r in results) / len(results)
        
        ws.cell(row=summary_row, column=7, value=round(total_sales_amount, 1))
        ws.cell(row=summary_row, column=13, value=round(total_variance, 1))
        ws.cell(row=summary_row, column=14, value=f"{avg_confidence*100:.1f}%")
        
        # 生成文件
        filename = f"product_forecast_{task.task_id}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        file_path = os.path.join(self.reports_dir, filename)
        wb.save(file_path)
        
        return file_path
    
    def _generate_historical_data_report(self, task, results, options):
        """生成历史数据报表"""
        # 获取相关的历史数据
        company_ids = json.loads(task.company_ids)
        product_ids = json.loads(task.product_ids) if task.product_ids else []
        
        query = HistoricalData.query.filter(HistoricalData.company_id.in_(company_ids))
        if product_ids:
            query = query.filter(HistoricalData.product_id.in_(product_ids))
        
        historical_records = query.order_by(
            HistoricalData.company_id,
            HistoricalData.product_id,
            HistoricalData.year
        ).all()
        
        # 创建Excel工作簿
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "历史数据分析"
        
        # 设置标题
        ws['A1'] = "重点产品历史数据分析报表"
        ws['A1'].font = Font(size=16, bold=True)
        ws['A1'].alignment = Alignment(horizontal='center')
        ws.merge_cells('A1:K1')
        
        # 设置报表信息
        ws['A3'] = f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        ws['A4'] = f"数据范围: {len(historical_records)}条历史记录"
        
        # 设置表头
        headers = [
            '序号', '公司名称', '产品名称', '年份', '销售量',
            '平均单价(元)', '销售额(万元)', '单位成本(元)',
            '单位毛利(元)', '同比增长率(%)', 'CAGR(%)'
        ]
        
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=6, column=col, value=header)
            cell.font = Font(bold=True)
            cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
            cell.alignment = Alignment(horizontal='center')
        
        # 填充历史数据
        for row, record in enumerate(historical_records, 7):
            ws.cell(row=row, column=1, value=row-6)
            ws.cell(row=row, column=2, value=record.company_name)
            ws.cell(row=row, column=3, value=record.product_name)
            ws.cell(row=row, column=4, value=record.year)
            ws.cell(row=row, column=5, value=round(record.sales_volume, 0))
            ws.cell(row=row, column=6, value=round(record.avg_price, 2))
            ws.cell(row=row, column=7, value=round(record.sales_amount, 1))
            ws.cell(row=row, column=8, value=round(record.unit_cost, 2))
            ws.cell(row=row, column=9, value=round(record.unit_profit, 2))
            ws.cell(row=row, column=10, value=round(record.yoy_growth_rate or 0, 1))
            ws.cell(row=row, column=11, value=round(record.cagr or 0, 1))
        
        # 设置列宽
        column_widths = [6, 15, 15, 8, 12, 12, 15, 12, 12, 12, 10]
        for col, width in enumerate(column_widths, 1):
            ws.column_dimensions[openpyxl.utils.get_column_letter(col)].width = width
        
        # 生成文件
        filename = f"historical_data_{task.task_id}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        file_path = os.path.join(self.reports_dir, filename)
        wb.save(file_path)
        
        return file_path
    
    def _generate_comprehensive_report(self, task, results, options):
        """生成综合报表"""
        # 创建Excel工作簿
        wb = openpyxl.Workbook()
        
        # 创建多个工作表
        # 1. 预测结果汇总
        ws1 = wb.active
        ws1.title = "预测结果汇总"
        self._create_prediction_summary_sheet(ws1, task, results)
        
        # 2. 详细预测数据
        ws2 = wb.create_sheet("详细预测数据")
        self._create_detailed_prediction_sheet(ws2, results)
        
        # 3. 差异分析
        ws3 = wb.create_sheet("差异分析")
        self._create_variance_analysis_sheet(ws3, results)
        
        # 4. AI分析报告
        ws4 = wb.create_sheet("AI分析报告")
        self._create_ai_analysis_sheet(ws4, results)
        
        # 生成文件
        filename = f"comprehensive_report_{task.task_id}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        file_path = os.path.join(self.reports_dir, filename)
        wb.save(file_path)
        
        return file_path
    
    def _create_prediction_summary_sheet(self, ws, task, results):
        """创建预测结果汇总表"""
        # 标题
        ws['A1'] = "预测结果汇总"
        ws['A1'].font = Font(size=16, bold=True)
        ws.merge_cells('A1:F1')
        
        # 基本信息
        ws['A3'] = "任务信息"
        ws['A3'].font = Font(bold=True)
        ws['A4'] = f"任务名称: {task.task_name}"
        ws['A5'] = f"预测年份: {', '.join(json.loads(task.prediction_years))}"
        ws['A6'] = f"涉及产品: {len(results)}个"
        ws['A7'] = f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        
        # 汇总统计
        ws['A9'] = "汇总统计"
        ws['A9'].font = Font(bold=True)
        
        total_sales = sum(r.predicted_sales_amount for r in results)
        total_variance = sum(r.total_variance for r in results)
        avg_confidence = sum(r.confidence_score for r in results) / len(results)
        
        ws['A10'] = f"预测销售额总计: {total_sales:.1f}万元"
        ws['A11'] = f"总差异: {total_variance:.1f}万元"
        ws['A12'] = f"平均置信度: {avg_confidence*100:.1f}%"
        
        # 按产品汇总
        ws['A14'] = "按产品汇总"
        ws['A14'].font = Font(bold=True)
        
        headers = ['产品名称', '预测销售额(万元)', '总差异(万元)', '置信度']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=15, column=col, value=header)
            cell.font = Font(bold=True)
            cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
        
        for row, result in enumerate(results, 16):
            ws.cell(row=row, column=1, value=result.product_name)
            ws.cell(row=row, column=2, value=round(result.predicted_sales_amount, 1))
            ws.cell(row=row, column=3, value=round(result.total_variance, 1))
            ws.cell(row=row, column=4, value=f"{result.confidence_score*100:.1f}%")
    
    def _create_detailed_prediction_sheet(self, ws, results):
        """创建详细预测数据表"""
        # 这里复用产品预测报表的逻辑
        pass
    
    def _create_variance_analysis_sheet(self, ws, results):
        """创建差异分析表"""
        ws['A1'] = "差异分析"
        ws['A1'].font = Font(size=16, bold=True)
        
        # 表头
        headers = ['产品名称', '量差(万元)', '价差(万元)', '成本差异(万元)', '总差异(万元)']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=3, column=col, value=header)
            cell.font = Font(bold=True)
            cell.fill = PatternFill(start_color="CCCCCC", end_color="CCCCCC", fill_type="solid")
        
        # 数据
        for row, result in enumerate(results, 4):
            ws.cell(row=row, column=1, value=result.product_name)
            ws.cell(row=row, column=2, value=round(result.volume_variance, 1))
            ws.cell(row=row, column=3, value=round(result.price_variance, 1))
            ws.cell(row=row, column=4, value=round(result.cost_variance, 1))
            ws.cell(row=row, column=5, value=round(result.total_variance, 1))
    
    def _create_ai_analysis_sheet(self, ws, results):
        """创建AI分析报告表"""
        ws['A1'] = "AI影响因素分析报告"
        ws['A1'].font = Font(size=16, bold=True)
        
        row = 3
        for result in results:
            ws.cell(row=row, column=1, value=f"{result.product_name} - {result.prediction_year}年分析")
            ws.cell(row=row, column=1).font = Font(bold=True)
            row += 1
            
            # 分析内容
            analysis = result.influence_factors_analysis or "暂无分析"
            ws.cell(row=row, column=1, value=analysis)
            ws.cell(row=row, column=1).alignment = Alignment(wrap_text=True, vertical='top')
            ws.row_dimensions[row].height = 100
            row += 3
    
    def get_report_file_path(self, filename):
        """获取报表文件路径"""
        return os.path.join(self.reports_dir, filename)
    
    def get_available_templates(self):
        """获取可用的报表模板"""
        return [
            {
                'id': template_id,
                'name': template_info['name'],
                'description': template_info['description'],
                'fields': template_info['fields']
            }
            for template_id, template_info in self.templates.items()
        ]
    
    def get_generated_reports(self, page=1, page_size=20):
        """获取已生成的报表列表"""
        try:
            # 获取报表目录下的所有文件
            files = []
            if os.path.exists(self.reports_dir):
                for filename in os.listdir(self.reports_dir):
                    if filename.endswith('.xlsx'):
                        file_path = os.path.join(self.reports_dir, filename)
                        stat = os.stat(file_path)
                        files.append({
                            'filename': filename,
                            'size': f"{stat.st_size / 1024 / 1024:.2f} MB",
                            'created_at': datetime.fromtimestamp(stat.st_ctime).isoformat(),
                            'modified_at': datetime.fromtimestamp(stat.st_mtime).isoformat()
                        })
            
            # 按修改时间排序
            files.sort(key=lambda x: x['modified_at'], reverse=True)
            
            # 分页
            start = (page - 1) * page_size
            end = start + page_size
            
            return {
                'items': files[start:end],
                'total': len(files),
                'page': page,
                'page_size': page_size,
                'pages': (len(files) + page_size - 1) // page_size
            }
            
        except Exception as e:
            raise e
    
    def preview_report(self, report_id):
        """预览报表"""
        # 这里可以实现报表预览功能
        # 目前返回模拟数据
        return {
            'report_id': report_id,
            'preview_available': False,
            'message': '报表预览功能开发中'
        }
    
    def get_generation_status(self, task_id):
        """获取报表生成状态"""
        # 检查是否有对应的报表文件
        pattern = f"*{task_id}*.xlsx"
        import glob
        files = glob.glob(os.path.join(self.reports_dir, pattern))
        
        if files:
            return {
                'status': 'completed',
                'files': [os.path.basename(f) for f in files],
                'generated_at': datetime.fromtimestamp(os.path.getmtime(files[0])).isoformat()
            }
        else:
            return {
                'status': 'not_found',
                'message': '未找到对应的报表文件'
            }

